﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Threading;
using System.Xml;
using Common.Utilities.Dispatcher;
using Microsoft.Office.Interop.Excel;
using DataTable=System.Data.DataTable;

namespace Workflows.Components.Reports
{
    /// <summary></summary>
    public class ExcelReportWorker:IWorker
    {
        private Thread workerThread;
        private ExcelReportInstruction instruction;
        private ExcelReportOutput output;
        private RunContext _Context;
        private Guid _TicketID;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        /// <summary></summary>
        public Thread WorkerThread
        {
            get { return workerThread; }
        }
        /// <summary></summary>
        public InstructionBase Instruction
        {
            get { return instruction; }
        }
        /// <summary></summary>
        public ResultBase Output
        {
            get { return output; }
        }
        /// <summary></summary>
        public Type ComponentType
        {
            get { return typeof(ExcelReportComponent); }
        }
        /// <summary></summary>
        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }
        /// <summary></summary>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (ExcelReportInstruction) request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (ExcelReportInstruction)request;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void DoWork()
        {
            if(this.WorkerStartedEvent !=null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread = Thread.CurrentThread;
            this.GenerateExcelReport();
        }
        /// <summary></summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread=new Thread(new ThreadStart(this.GenerateExcelReport));
            this.workerThread.Start();
        }
        /// <summary></summary>
        public void CancelWork()
        {
            if(this.workerThread !=null && this.workerThread.IsAlive)
            {
                this.workerThread.Abort();
                this.workerThread.Join();

                if(this.WorkerCancelledEvent !=null)
                {
                    this.WorkerCancelledEvent(this._TicketID, this.GetType(), this.instruction);
                }
            }
        }

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        #region work
        private void GenerateExcelReport()
        {
            Application excelApp = new Application();
            SqlConnection conn=new SqlConnection(this.instruction.ConnectionString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 0;
                XmlDocument xDoc = new XmlDocument();
                xDoc.LoadXml(this.instruction.Template);
                xDoc.Save(this.instruction.ReportFilePath);
                
                excelApp.Visible = false;
                Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(
                    this.instruction.ReportFilePath, 0, false, 5, "", "", true,
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                    "\t", false, false, 0, true, false, false);
                excelApp.DisplayAlerts = false;

                this.instruction.DataRegions.Sort();
                int dataRegionUpdated = 0;

                foreach (ExcelDataRegion region in this.instruction.DataRegions)
                {
                    if (!string.IsNullOrEmpty(region.Sql))
                    {
                        if (this.instruction.ParameterNameValues != null && this.instruction.ParameterNameValues.Count > 0)
                        {
                            foreach (string paramName in this.instruction.ParameterNameValues.Keys)
                            {
                                region.Sql = region.Sql.Replace("@" + paramName,
                                                                this.instruction.ParameterNameValues[paramName]);
                            }
                        }
                    }
                    
                    foreach (Microsoft.Office.Interop.Excel.Worksheet wkSheet in excelWorkbook.Worksheets)
                    {
                        if (wkSheet.Name == region.WorksheetName)
                        {
                            if (this.WorkerStatusChangedEvent != null)
                            {
                                string msg =
                                    string.Format("Excel report worker is updating range {0} of worksheet {1}...",
                                                  region.Range, region.WorksheetName);
                                int percent = (dataRegionUpdated * 100) / this.instruction.DataRegions.Count;
                                this.WorkerStatusChangedEvent(this._TicketID, this.GetType(), this.instruction, this.WorkflowName, this.ComponentName, msg,
                                                              percent);
                            }

                            switch (region.FillAction)
                            {
                                case ExcelDataFillAction.FillValue:
                                    cmd.CommandText = region.Sql;
                                    cmd.CommandType = CommandType.Text;
                                    try
                                    {
                                        WorksheetHelper.SetValue(wkSheet, region.Range.Replace("$", ""), cmd.ExecuteScalar());
                                    }
                                    catch(Exception ex1)
                                    {
                                        WorksheetHelper.SetValue(wkSheet, region.Range.Replace("$",""), ex1.Message);
                                    }
                                    break;
                                case ExcelDataFillAction.FillTable:
                                    SqlDataAdapter daSelect = new SqlDataAdapter(region.Sql, conn);
                                    DataSet dsSelect = new DataSet();
                                    daSelect.Fill(dsSelect);
                                    DataTable dtSelect = dsSelect.Tables[0];
                                    string topLeftCellAddr = region.Range.Replace("$", "");
                                    if (topLeftCellAddr.IndexOf(":") > 0)
                                    {
                                        topLeftCellAddr = topLeftCellAddr.Substring(0, topLeftCellAddr.IndexOf(":"));
                                    }
                                    Dictionary<string, int> cellIndexes = new Dictionary<string, int>();
                                    if (region.ExcelColumnBindings != null && region.ExcelColumnBindings.Count > 0)
                                    {
                                        foreach (string dataColName in region.ExcelColumnBindings.Keys)
                                        {
                                            string excelColName = region.ExcelColumnBindings[dataColName];
                                            int excelColIndex = WorksheetHelper.GetColumnIndex(excelColName);
                                            cellIndexes.Add(dataColName, excelColIndex);
                                        }
                                    }
                                    else
                                    {
                                        int startColumnIndex = WorksheetHelper.GetColumnIndex(topLeftCellAddr);
                                        for (int i = 0; i < dtSelect.Columns.Count; i++)
                                        {
                                            int colIndex = startColumnIndex + i;
                                            cellIndexes.Add(dtSelect.Columns[i].ColumnName, colIndex);
                                        }
                                    }
                                    try
                                    {
                                        WorksheetHelper.FillTable(wkSheet, topLeftCellAddr, cellIndexes, dtSelect);
                                    }
                                    catch(Exception ex1)
                                    {
                                        if(this.WorkerErredEvent !=null)
                                        {
                                            this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction,
                                                                  ex1.Message, false);
                                        }
                                    }
                                    break;
                                case ExcelDataFillAction.FillTableByKey:
                                    SqlDataAdapter daSelect2 = new SqlDataAdapter(region.Sql, conn);
                                    DataSet dsSelect2 = new DataSet();
                                    daSelect2.Fill(dsSelect2);
                                    DataTable dtSelect2 = dsSelect2.Tables[0];
                                    string topLeftCellAddr2 = region.Range.Replace("$", "");
                                    if (topLeftCellAddr2.IndexOf(":") > 0)
                                    {
                                        topLeftCellAddr2 = topLeftCellAddr2.Substring(0, topLeftCellAddr2.IndexOf(":"));
                                    }
                                    Dictionary<string, int> cellIndexes2 = new Dictionary<string, int>();
                                    string keyPosition = region.KeyPosition.Replace("$", "");
                                    if ((!string.IsNullOrEmpty(region.KeyName)) && (!string.IsNullOrEmpty(keyPosition)))
                                    {
                                        cellIndexes2[region.KeyName] = WorksheetHelper.GetColumnIndex(keyPosition);
                                    }

                                    if (region.ExcelColumnBindings != null && region.ExcelColumnBindings.Count > 0)
                                    {
                                        foreach (string dataColName in region.ExcelColumnBindings.Keys)
                                        {
                                            string excelColName = region.ExcelColumnBindings[dataColName];
                                            int excelColIndex = WorksheetHelper.GetColumnIndex(excelColName);
                                            if (!cellIndexes2.ContainsKey(dataColName))
                                            {
                                                cellIndexes2.Add(dataColName, excelColIndex);
                                            }
                                        }
                                    }
                                    else
                                    {
                                        int startColumnIndex2 = WorksheetHelper.GetColumnIndex(topLeftCellAddr2);
                                        for (int i = 0; i < dtSelect2.Columns.Count; i++)
                                        {
                                            if (!cellIndexes2.ContainsKey(dtSelect2.Columns[i].ColumnName))
                                            {
                                                startColumnIndex2++;
                                                cellIndexes2.Add(dtSelect2.Columns[i].ColumnName, startColumnIndex2);
                                            }
                                        }
                                    }
                                    try 
                                    {
                                        WorksheetHelper.FillTable(wkSheet, topLeftCellAddr2, cellIndexes2, dtSelect2,
                                                                  region.KeyName);
                                    }
                                    catch(Exception ex1)
                                    {
                                        if (this.WorkerErredEvent != null)
                                            this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction,
                                                                  ex1.Message, false);
                                    }
                                    break;
                                default:
                                    break;
                            }
                            break;
                        }

                        dataRegionUpdated++;
                    }
                }

                excelWorkbook.Close(true, this.instruction.ReportFilePath, null);
                

                this.output=new ExcelReportOutput(this.instruction.OwnerComponentID,this._TicketID,this.instruction.ReportFilePath);
                if(this.WorkerFinishedEvent !=null)
                {
                    this.WorkerFinishedEvent(this._TicketID, this.GetType(), this.instruction, this.output);
                }
            }
            catch(Exception ex)
            {
                if (this.WorkerErredEvent != null)
                {
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex.ToString(),true);
                }
            }
            finally
            {
                conn.Close();
                excelApp.Quit();
            }
        }
        #endregion
    }
}
